package com.sky.utils;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
 
/**
 * Excel工具类
 *
 * @author zhangdj
 * @date 2019/8/20
 */
public class ExcelUtil {
 
    /**
     * 导出数据到Excel
     *
     * @param response
     * @param headers       表头
     * @param fileName      文件名(不需要加后缀名)
     * @param list          要导出的数据
     * @param sheetName     工作簿名称
     * @param propertyNames 要写入的实例中的属性名
     * @param <T>           要写入数据的实例类型
     * @throws Exception
     */
    public static <T> void exportExcel(HttpServletResponse response, String[] headers,
                                       String fileName, List<T> list, String sheetName, String[] propertyNames) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //第一行 表头
        HSSFRow heardersRow = sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            heardersRow.createCell(i).setCellValue(headers[i]);
        }
        for (int i = 0; i < list.size(); i++) {
            //从第二行开始写数据
            HSSFRow row = sheet.createRow(i + 1);
            T t = list.get(i);
            //得到T类型中的所有字段
            Field[] fields = t.getClass().getDeclaredFields();
            if (propertyNames != null && propertyNames.length != 0) {
                //遍历要写入Excel的属性值字段 如果与类中的属性字段匹配到了 就写值
                for (int j = 0; j < propertyNames.length; j++) {
                    for (int k = 0; k < fields.length; k++) {
                        Field field = fields[k];
                        if (!field.getName().equals(propertyNames[j])) {
                            continue;
                        }
                        //字段名称匹配 向单元格中写内容
                        setCellValue(t, field, row, j);
                    }
                }
            } else {
                for (int j = 0; j < fields.length; j++) {
                    Field field = fields[j];
                    setCellValue(t, field, row, j);
                }
            }
        }
        response.setContentType("application/vnd.ms-excel;charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes(), "ISO8859-1") + ".xlsx");
        workbook.write(response.getOutputStream());
        if (workbook != null) {
            workbook.close();
        }
    }
 
    /**
     * 指定单元格数值
     *
     * @param t
     * @param field
     * @param row       单元格所在行
     * @param cellIndex 单元格所在列
     * @throws Exception
     */
    private static <T> void setCellValue(T t, Field field, HSSFRow row, int cellIndex) throws Exception {
        HSSFCell cell = row.createCell(cellIndex);
        Object propertyValue = getPropertyValue(t, field);
        if (propertyValue != null) {
            if (propertyValue instanceof Date) {
                String s = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(propertyValue);
                cell.setCellValue(s);
            } else {
                cell.setCellValue(propertyValue.toString());
            }
        }
    }
 
    /**
     * 获取属性值
     *
     * @param t     实例
     * @param field 实例中的字段
     * @return
     * @throws Exception
     */
    private static <T> Object getPropertyValue(T t, Field field) throws Exception {
        // 获取Bean的某个属性的描述符
        PropertyDescriptor propertyDescriptor = new PropertyDescriptor(field.getName(), t.getClass());
        // 获得用于读取属性值的方法
        Method method = propertyDescriptor.getReadMethod();
        // 读取属性值
        return method.invoke(t);
    }
}